from typing import List, Dict, Any, Optional
import json
from datetime import datetime


class SQLInsertGenerator:
    def __init__(self, table_structures: List[Dict], db_type: str = "mysql"):
        """
        初始化生成器
        :param table_structures: 来自 tables 字段的列表，每个元素含 name/struts
        :param db_type: "mysql" 或 "oracle"
        """
        self.db_type = db_type.lower()
        # 构建表名 -> 字段结构映射，便于快速查找
        self.table_schema_map = {
            table["name"]: {
                "fields": {f["field"]: f for f in table["struts"]},
                "field_order": [f["field"] for f in table["struts"]],  # 保持顺序
            }
            for table in table_structures
        }

    def generate_inserts(self, mock_data: Dict[str, List[Dict]]) -> List[str]:
        """
        根据 mock 数据生成 INSERT 语句列表
        :param mock_data: {"表名": [行数据1, 行数据2, ...]}
        :return: SQL 语句列表
        """
        sql_statements = []
        for table_name, rows in mock_data.items():
            if table_name not in self.table_schema_map:
                print(f"表 {table_name} 未在结构定义中，跳过生成 INSERT")
                continue

            schema = self.table_schema_map[table_name]
            field_order = schema["field_order"]
            fields_map = schema["fields"]

            for row in rows:
                # 按结构定义的字段顺序提取值
                values = []
                for field in field_order:
                    value = row.get(field)
                    field_info = fields_map.get(field, {})
                    py_type = field_info.get("type", "str")
                    escaped_value = self._escape_value(value, py_type)
                    values.append(escaped_value)

                # 生成 INSERT 语句
                columns = ", ".join(
                    f"`{f}`" if self.db_type == "mysql" else f'"{f}"'
                    for f in field_order
                )
                values_str = ", ".join(values)
                if self.db_type == "oracle":
                    sql = (
                        f'INSERT INTO "{table_name}" ({columns}) VALUES ({values_str})'
                    )
                else:  # mysql
                    sql = (
                        f"INSERT INTO `{table_name}` ({columns}) VALUES ({values_str})"
                    )
                sql_statements.append(f"{sql};")

        return sql_statements

    def _escape_value(self, value: Any, py_type: str) -> str:
        """
        根据 Python 类型转义值，适配 SQL 语法
        """
        if value is None:
            return "NULL"

        if py_type in ("int", "float", "bool"):
            return str(
                value
            ).upper()  # BOOL: True/False → TRUE/FALSE (Oracle/MySQL 都支持)

        if py_type == "datetime":
            if isinstance(value, str):
                # 假设格式是 "2025-11-12 11:20:30"
                return self._format_datetime_str(value)
            elif isinstance(value, datetime):
                return self._format_datetime_obj(value)
            else:
                return "NULL"

        if py_type == "date":
            if isinstance(value, str):
                # 假设格式是 "2025-11-12"
                return f"'{value}'"
            elif isinstance(value, datetime):
                return f"'{value.strftime('%Y-%m-%d')}'"
            else:
                return "NULL"

        if py_type == "time":
            if isinstance(value, str):
                return f"'{value}'"
            elif isinstance(value, datetime):
                return f"'{value.strftime('%H:%M:%S')}'"
            else:
                return "NULL"

        # 默认：字符串类型，转义单引号
        str_val = str(value).replace("'", "''")
        return f"'{str_val}'"

    def _format_datetime_str(self, dt_str: str) -> str:
        """格式化字符串形式的 datetime"""
        if self.db_type == "oracle":
            # Oracle 需要 TO_DATE
            return f"TO_DATE('{dt_str}', 'YYYY-MM-DD HH24:MI:SS')"
        else:
            return f"'{dt_str}'"

    def _format_datetime_obj(self, dt: datetime) -> str:
        """格式化 datetime 对象"""
        dt_str = dt.strftime("%Y-%m-%d %H:%M:%S")
        if self.db_type == "oracle":
            return f"TO_DATE('{dt_str}', 'YYYY-MM-DD HH24:MI:SS')"
        else:
            return f"'{dt_str}'"


if __name__ == "__main__":
    # 你的原始结构（简化版，只取 tables）
    table_structures = [
        {
            "name": "A",
            "struts": [
                {"field": "FIELD", "type": "str"},
                {"field": "FIELD2", "type": "str"},
                {"field": "CREATE_TIME", "type": "datetime"},
                {"field": "LOGID", "type": "str"},
            ],
            "desc": "",
        },
        {
            "name": "LOG",
            "struts": [
                {"field": "ID", "type": "str"},
                {"field": "CREATE_TIME", "type": "datetime"},
            ],
            "desc": "",
        },
    ]

    # Mock 数据
    mock_data = {
        "A": [
            {
                "FIELD": "1212",
                "FIELD2": "2222",
                "CREATE_TIME": "2025-11-12 11:20:30",
                "LOGID": "haha",
            },
            {
                "FIELD": "3333",
                "FIELD2": "4444",
                "CREATE_TIME": "2025-12-01 08:30:00",
                "LOGID": "hehe",
            },
        ],
        "LOG": [{"ID": "LOG001", "CREATE_TIME": "2025-11-12 11:20:30"}],
    }

    # 生成 MySQL 语句
    generator = SQLInsertGenerator(table_structures, db_type="mysql")
    sqls = generator.generate_inserts(mock_data)
    for sql in sqls:
        print(sql)

    print("\n=== Oracle ===\n")

    # 生成 Oracle 语句
    generator = SQLInsertGenerator(table_structures, db_type="oracle")
    sqls = generator.generate_inserts(mock_data)
    for sql in sqls:
        print(sql)
